package com.example.submit_system.mapper;

import com.example.submit_system.model.TestLpyEmployee;
import com.example.submit_system.model.TestLpyEmployeeSubmit;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Update;

import java.sql.Date;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Mapper
public interface DataBaseTableMapper {

    /*
     * 调用数据转换存储过程
     * */
    public void producerSingle(String date);

    /*
     * 查询test_lpy_employee_submit表的所有数据
     * */
    public ArrayList<TestLpyEmployeeSubmit> selectAll();

    /*
     * 批量导入接口
     * */
    public void batchInsert(List<TestLpyEmployee> list);

    // 清空test_lpy_employee表
    void deleteAll();

    // 查询test_lpy_employee表的所有数据
    @Select("SELECT employee_id, name, gender, age, department, position, entry_date FROM test_lpy_employee")
    @Results({
            @Result(property = "employeeId", column = "employee_id"),
            @Result(property = "name", column = "name"),
            @Result(property = "gender", column = "gender"),
            @Result(property = "age", column = "age"),
            @Result(property = "department", column = "department"),
            @Result(property = "position", column = "position"),
            @Result(property = "entryDate", column = "entry_date")
    })
    List<TestLpyEmployee> selectAllEmployees();

    // 分页查询员工数据
    //@Results:手动定义数据库字段与 Java 实体类属性的映射关系
    @Select("SELECT employee_id, name, gender, age, department, position, entry_date FROM test_lpy_employee LIMIT #{size} OFFSET #{offset}")
    @Results({
            @Result(property = "employeeId", column = "employee_id"),
            @Result(property = "name", column = "name"),
            @Result(property = "gender", column = "gender"),
            @Result(property = "age", column = "age"),
            @Result(property = "department", column = "department"),
            @Result(property = "position", column = "position"),
            @Result(property = "entryDate", column = "entry_date")
    })
    List<TestLpyEmployee> selectAllEmployeesPaginated(@Param("offset") int offset, @Param("size") int size);

    // 获取员工总数
    @Select("SELECT COUNT(*) FROM test_lpy_employee")
    int getTotalEmployeeCount();

    List<TestLpyEmployee> filterByDate(@Param("startDate") String startDate, @Param("endDate") String endDate);

    // 根据日期范围分页查询员工数据
    @Select("SELECT employee_id, name, gender, age, department, position, entry_date FROM test_lpy_employee WHERE entry_date BETWEEN #{startDate} AND #{endDate} LIMIT #{size} OFFSET #{offset}")
    @Results({
            @Result(property = "employeeId", column = "employee_id"),
            @Result(property = "name", column = "name"),
            @Result(property = "gender", column = "gender"),
            @Result(property = "age", column = "age"),
            @Result(property = "department", column = "department"),
            @Result(property = "position", column = "position"),
            @Result(property = "entryDate", column = "entry_date")
    })
    List<TestLpyEmployee> filterByDatePaginated(@Param("startDate") String startDate, @Param("endDate") String endDate, @Param("offset") int offset, @Param("size") int size);

    // 获取指定日期范围内的员工总数
    @Select("select count(*) from test_lpy_employee where entry_date BETWEEN #{startDate} and #{endDate}")
    int getTotalEmployeeCountFiltered(@Param("startDate") String startDate, @Param("endDate") String endDate);

    // 根据员工ID删除员工数据
    @Delete("delete from test_lpy_employee WHERE employee_id = #{employeeId}")
    void deleteEmployeeById(@Param("employeeId") Integer employeeId);

    // 更新员工数据
    @Update("update test_lpy_employee set name = #{name}, gender = #{gender}, age = #{age}, department = #{department}, position = #{position}, entry_date = #{entryDate} WHERE employee_id = #{employeeId}")
    void updateEmployee(TestLpyEmployee employee);

    void insertEmployee(TestLpyEmployee employee);

    // 批量删除员工
    void batchDelete(@Param("employeeIds") List<Integer> employeeIds);


    // 查询所有报送表数据
    //数据库字段与 Java 实体类属性之间的映射
    @Select("select employee_id, name, gender, age, department, position, entry_date, file_date FROM test_lpy_employee_submit")
    @Results({
            @Result(property = "employeeId", column = "employee_id"),
            @Result(property = "name", column = "name"),
            @Result(property = "gender", column = "gender"),
            @Result(property = "age", column = "age"),
            @Result(property = "department", column = "department"),
            @Result(property = "position", column = "position"),
            @Result(property = "entryDate", column = "entry_date"),
            @Result(property = "fileDate", column = "file_date")
    })
    List<TestLpyEmployeeSubmit> selectAllSubmit();

    // 根据日期查询报送表数据
//    @Select("SELECT employee_id, name, gender, age, department, position, entry_date, file_date FROM test_lpy_employee_submit WHERE file_date = #{date}")
//    @Results({
//            @Result(property = "employeeId", column = "employee_id"),
//            @Result(property = "name", column = "name"),
//            @Result(property = "gender", column = "gender"),
//            @Result(property = "age", column = "age"),
//            @Result(property = "department", column = "department"),
//            @Result(property = "position", column = "position"),
//            @Result(property = "entryDate", column = "entry_date"),
//            @Result(property = "fileDate", column = "file_date")
//    })

    //List<TestLpyEmployeeSubmit> selectEmployeesSubmitByDate(@Param("date") java.sql.Date date);

    // 分页查询所有test_lpy_employee_submit表数据
    List<TestLpyEmployeeSubmit> selectAllSubmitPaginated(@Param("offset") int offset, @Param("size") int size);

    // 获取test_lpy_employee_submit表数据总数
    int getTotalSubmitCount();

    //根据日期分页查询报送表数据
    List<TestLpyEmployeeSubmit> selectEmployeesSubmitByDatePaginated(@Param("date") java.sql.Date date, @Param("offset") int offset, @Param("size") int size);

    //获取按日期筛选的报送表数据总数
    Integer getTotalSubmitCountByDate(@Param("date") java.sql.Date date);

}

